﻿/******************************************************   
创建者: 梁家健
创建时间: 2017-03-15
修改:
修改时间: 
功能: 上新计划表
 ******************************************************/


using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.library.Helper;
using george.Data;
using Nancy.Extensions;
using NHibernateVertica;
using PlanAPI.model;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using com.nm.library;
using VerticaAPI;

namespace PlanAPI
{
    [Description("上新计划表")]
    public class PLBasSalePlanHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        /// Writer：梁家健
        /// Create Date：2017-03-15
        [MustLogin]
        [Description("上新计划表列表")]
        [ReturnType(typeof(PaperData<PLBasSalePlanModel.ListModel>))]
        public ApiResult GetPLBasSalePlanList([ParamDescription("商品区域")] string ParentAreaId = "", 
            [ParamDescription("年份")] string Yearv = "", [ParamDescription("月份")] string Monthv = "", 
            [ParamDescription("季节")] string SeasonName = "", [ParamDescription("页码")] int Page = 1, [ParamDescription("页数")] int Size = 10)
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var count = ReturnPLBasSalePlanCount(ParentAreaId,Yearv, Monthv, SeasonName);
                var dt = ReturnPLBasSalePlanList(ParentAreaId, Yearv, Monthv, SeasonName, Page, Size);
                var result = PaperData<PLBasSalePlanModel.ListModel>.Create(count, Size, Page);
                var list = ModelConvertHelper<PLBasSalePlanModel.ListModel>.ConvertToModel(dt);
                result.Items = list;
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 上新计划表
        /// </summary>
        /// <param name="ParentAreaId">商品区域</param>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <returns>返回上新计划表列表</returns>
        /// Writer：梁家健
        /// Create Date：2017-03-15
        public DataTable ReturnPLBasSalePlanList(string ParentAreaId, string Yearv, string Monthv, string SeasonName, int Page = 1, int Size = 10)
        {
            try
            {
                var sql = $@"select *,dictName as ParentAreaName from (
SELECT  * ,
        ROW_NUMBER() OVER ( ORDER BY ParentAreaID, Yearv DESC, Monthv DESC, Weekv DESC ) AS Row
FROM    ngoods.ngoods_PL_Bas_SalePlan plan
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_SD_Mat_Season
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                   ) season ON plan.SeasonID = season.SeasonID
                               AND plan.ExtrSource = season.ExtrSource
        INNER JOIN ( SELECT *
                     FROM   ndw.ndw_global_dictionary
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                   ) dict ON plan.ParentAreaID = dict.dictValue
                            AND plan.ExtrSource = dict.ExtrSource
                            AND plan.CompanyID = dict.CompanyID
WHERE   plan.ExtrSource = '{UserInfo.UserExtrSource}'
        AND plan.CompanyID = '{UserInfo.UserCompanyId}'
{(string.IsNullOrWhiteSpace(ParentAreaId) ? "" : $@" and ParentAreaID = '{ParentAreaId}' ")}
{(string.IsNullOrWhiteSpace(Yearv) ? "" : $@" and Yearv = '{Yearv}' ")}
{(string.IsNullOrWhiteSpace(Monthv) ? "" : $@" and Monthv = '{Monthv}' ")}
{(string.IsNullOrWhiteSpace(SeasonName) ? "" : $@" and season.SeasonName = '{SeasonName}' ")}
) table1 where Row BETWEEN ({Page}-1)*{Size}+1 AND {Page}*{Size}";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 统计上新计划表
        /// </summary>
        /// <param name="ParentAreaId">商品区域</param>
        /// <returns>返回上新计划表列表统计个数</returns>
        /// Writer：梁家健
        /// Create Date：2017-03-15
        public int ReturnPLBasSalePlanCount(string ParentAreaId, string Yearv, string Monthv, string SeasonName)
        {
            try
            {
                var sql = $@"SELECT  COUNT(1)
FROM    ngoods.ngoods_PL_Bas_SalePlan plan
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_SD_Mat_Season
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                   ) season ON plan.SeasonID = season.SeasonID
                               AND plan.ExtrSource = season.ExtrSource
        INNER JOIN ( SELECT *
                     FROM   ndw.ndw_global_dictionary
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                   ) dict ON plan.ParentAreaID = dict.dictValue
                            AND plan.ExtrSource = dict.ExtrSource
                            AND plan.CompanyID = dict.CompanyID
WHERE   plan.ExtrSource = '{UserInfo.UserExtrSource}'
        AND plan.CompanyID = '{UserInfo.UserCompanyId}'
{(string.IsNullOrWhiteSpace(ParentAreaId) ? "" : $@" and ParentAreaID = '{ParentAreaId}' ")}
{(string.IsNullOrWhiteSpace(Yearv) ? "" : $@" and Yearv = '{Yearv}' ")}
{(string.IsNullOrWhiteSpace(Monthv) ? "" : $@" and Monthv = '{Monthv}' ")}
{(string.IsNullOrWhiteSpace(SeasonName) ? "" : $@" and season.SeasonName = '{SeasonName}' ")}";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-03-15
        [JsonType]
        [MustLogin]
        [Description("修改上新计划表")]
        [ReturnType(typeof(int),"0-成功")]
        public ApiResult UpdatePLBasSalePlan(PLBasSalePlanModel.ModifyModel param)
        {
            try
            {
                if (param == null) return Error("参数对象为空");
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                if (param.list != null)
                {
                    param.list.ToList().ForEach(d =>
                    {
                        d.CompanyID = UserInfo.UserCompanyId;
                        d.ExtrSource = UserInfo.UserExtrSource;
                        d.CheckState = 1;
                    });
                    MergePLBasSalePlan(param.list);
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 创建临时表
        /// </summary>
        /// <param name="list"></param>
        /// Writer：梁家健
        /// Create Date：2017-06-09
        public void CreateTempTable(DataTable dt,string TempTableName,Dictionary<string,string> Cesd)
        {
            try
            {
                var sql = $@"CREATE TABLE {TempTableName}
(
    CompanyID varchar(30),
    ExtrSource varchar(30),
    Yearv int,
    Weekv int,
    Monthv int,
    ParentAreaName varchar(80),
    SeasonName varchar(120),
    UpdateRate numeric(8,4),
    Remark varchar(150)
)";
                dt.Columns.Add("ExtrSource", System.Type.GetType("System.String"));
                foreach (DataRow item in dt.Rows)
                {
                    var companyID = item["CompanyID"].ToString();
                    item["ExtrSource"] = Cesd.Keys.Contains(companyID) ? Cesd[companyID] : "";
                }
                Helper.ExecuteSqlTran(dt, TempTableName, sql);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 删除临时表
        /// </summary>
        /// <param name="TempTableName"></param>
        /// Writer：梁家健
        /// Create Date：2017-06-09
        public void DropTempTable(string TempTableName)
        {
            try
            {
                Helper.DropTempTable(TempTableName);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 导入数据
        /// </summary>
        /// <param name="TempTableName"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-06-09
        public DataTable ImportPLBasSalePlan(string TempTableName, Dictionary<string, string> Cesd)
        {
            try
            {
                var sql = $@"SELECT  temp.CompanyID ,
        temp.Yearv ,
        temp.Weekv ,
        temp.Monthv ,
        temp.ParentAreaName ,
        temp.SeasonName ,
        temp.UpdateRate ,
        temp.Remark ,
        dict.dictValue AS ParentAreaID ,
        season.SeasonID
FROM    {TempTableName} temp
        LEFT JOIN ( SELECT  *
                    FROM    ndw.ndw_global_dictionary
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) dict ON temp.ParentAreaName = dict.dictName
                            AND temp.CompanyID = dict.CompanyID
                            AND temp.ExtrSource = dict.ExtrSource
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_SD_Mat_Season
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                  ) season ON temp.SeasonName = season.SeasonName
                              AND temp.ExtrSource = season.ExtrSource";
                var companyID = UserInfo.UserCompanyId;
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<PLBasSalePlanModel.ListModel>.ConvertToModel(dt);
                var newList =
                    list.Where(
                            d =>
                                d.CompanyID == companyID && Cesd.Keys.Contains(d.CompanyID) &&
                                d.ParentAreaID != null && d.SeasonID != null && d.UpdateRate >= 0 && d.UpdateRate <= 100)
                        .Select(d => d);
                var resultList = new List<PLBasSalePlanModel.ListModel>();
                foreach (var item in newList)
                {
                    var begin = CalendarHandler.GetWeekOfYear(new DateTime((int) item.Yearv, (int) item.Monthv, 1));
                    var end =
                        CalendarHandler.GetWeekOfYear(
                            new DateTime((int) item.Yearv, (int) item.Monthv, 1).AddMonths(1).AddDays(-1));
                    item.ExtrSource = Cesd[item.CompanyID];
                    for (var i = begin; i <= end ; i++)
                    {
                        if (item.Weekv != i) continue;
                        resultList.Add(item);
                        break;
                    }
                }
                MergePLBasSalePlan(resultList.Select(d => new PLBasSalePlanModel.NeceModel
                {
                    CompanyID = d.CompanyID,
                    ExtrSource = d.ExtrSource,
                    Yearv = d.Yearv,
                    Monthv = d.Monthv,
                    Weekv = d.Weekv,
                    SeasonID = d.SeasonID,
                    ParentAreaID = d.ParentAreaID,
                    Remark = d.Remark,
                    UpdateRate = d.UpdateRate,
                    CheckState = 1L,
                }).ToList());
                var errorList = list.Except(resultList).ToList();
                var errorDT = DataTableHelper.LinqToDataTable(errorList);
                //if (errorDT.Rows.Count > 0)
                //{
                //    errorDT.Columns.Remove("ParentAreaID");
                //    errorDT.Columns.Remove("SeasonID");
                //}
                return errorDT;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 枚举
        /// </summary>
        /// <param name="list"></param>
        /// Writer：梁家健
        /// Create Date：2017-06-09
        public void MergePLBasSalePlan(IList<PLBasSalePlanModel.NeceModel> list)
        {
            try
            {
                var uid = Guid.NewGuid().ToString().Replace("-", "");
                var tempTableName = $" ngoods.ngoods_{uid} ";
                const string targetTableName = @" ngoods.ngoods_PL_Bas_SalePlan ";
                var dt =
                    DataTableHelper.LinqToDataTable(
                        list.DistinctBy(d => new {d.CompanyID, d.Yearv, d.Weekv, d.Monthv, d.ParentAreaID, d.SeasonID}));
                const string onConditions = @" targetTable.CompanyID=sourceTable.CompanyID
         AND targetTable.ExtrSource=sourceTable.ExtrSource
         AND targetTable.Yearv=sourceTable.Yearv
         AND targetTable.Monthv=sourceTable.Monthv
         AND targetTable.Weekv=sourceTable.Weekv
         AND targetTable.ParentAreaID=sourceTable.ParentAreaID
         AND targetTable.SeasonID=sourceTable.SeasonID ";
                const string setConditions = @" UpdateRate = sourceTable.UpdateRate/100 ,
                                            CheckState = sourceTable.CheckState ,
                                            Remark = sourceTable.Remark ";
                const string insertConditions = @" CompanyID ,
                      ExtrSource ,
                      Yearv ,
                      Monthv ,
                      Weekv ,
                      ParentAreaID ,
                      SeasonID ,
                      UpdateRate ,
                      CheckState ,
                      Remark ";
                const string valuesConditions = @" sourceTable.CompanyID ,
                      sourceTable.ExtrSource ,
                      sourceTable.Yearv ,
                      sourceTable.Monthv ,
                      sourceTable.Weekv ,
                      sourceTable.ParentAreaID ,
                      sourceTable.SeasonID ,
                      sourceTable.UpdateRate/100 ,
                      sourceTable.CheckState ,
                      sourceTable.Remark ";
                Helper.Merge(tempTableName, targetTableName, dt, onConditions, setConditions, insertConditions,
                    valuesConditions, true);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 导出模板
        /// </summary>
        /// <param name="Keys"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-03-15
        public DataTable ExportPLBasSalePlanTemp(string Keys)
        {
            try
            {
                var sql = $@"SELECT  plan.CompanyID ,
        plan.Yearv ,
        plan.Weekv ,
        plan.Monthv ,
        dictName AS ParentAreaName ,
        season.SeasonName ,
        plan.UpdateRate ,
        plan.Remark
FROM    ngoods.ngoods_PL_Bas_SalePlan plan
        INNER JOIN ( SELECT *
                     FROM   nods.nods_erp_SD_Mat_Season
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                   ) season ON plan.SeasonID = season.SeasonID
                               AND plan.ExtrSource = season.ExtrSource
        INNER JOIN ( SELECT *
                     FROM   ndw.ndw_global_dictionary
                     WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                   ) dict ON plan.ParentAreaID = dict.dictValue
                             AND plan.ExtrSource = dict.ExtrSource
                             AND plan.CompanyID = dict.CompanyID
WHERE   Yearv = {DateTime.Now.Year}
        AND plan.CompanyID = '{UserInfo.UserCompanyId}'
        AND plan.ExtrSource = '{UserInfo.UserExtrSource}'
         {(string.IsNullOrEmpty(Keys) ? "" : Keys)}
ORDER BY plan.ParentAreaID ,
        plan.Yearv DESC ,
        plan.SeasonID DESC ,
        plan.Monthv DESC ,
        plan.Weekv DESC";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 根据年份、月份返回上新计划表数据
        /// </summary>
        /// <param name="Yearv"></param>
        /// <param name="Monthv"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-03-20
        public IList<PLBasSalePlanModel.NeceModel> ReturnAllPLBasSalePlan(int Yearv,int Monthv)
        {
            try
            {
                var sql = $@"SELECT  *
FROM    ngoods.ngoods_PL_Bas_SalePlan
WHERE   Yearv = {Yearv}
        AND Monthv = {Monthv}
        AND CompanyID = '{UserInfo.UserCompanyId}'
        AND ExtrSource = '{UserInfo.UserExtrSource}'";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                return ModelConvertHelper<PLBasSalePlanModel.NeceModel>.ConvertToModel(dt);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-04-15
        [MustLogin]
        [Description("企划年月周列表")]
        [ReturnType(typeof(IList<PLBasSalePlanModel.DateModel>))]
        public ApiResult GetWeekByYearAndMonth()
        {
            try
            {
                if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
                {
                    return Error("该用户不属于任何公司!");
                }
                if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
                {
                    return Error("该用户所在公司没有任何数据源!");
                }
                var set = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
                if (set == null) return Error("没有要修改企划的月份");
                var dt = ReturnWeekByYearAndMonth(set.Yearv.Value, set.Monthv.Value);
                var result = ModelConvertHelper<PLBasSalePlanModel.DateModel>.ConvertToModel(dt);
                return Success(result);
            }
            catch(Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 返回企划年月周列表
        /// </summary>
        /// <param name="Yearv"></param>
        /// <param name="Monthv"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-04-15
        public DataTable ReturnWeekByYearAndMonth(long Yearv, long Monthv)
        {
            try
            {
                var sql = $@"SELECT  Yearv ,
        Monthv ,
        Weekv
FROM    ngoods.ngoods_PL_Bas_SalePlan
WHERE   Yearv = {Yearv}
        AND Monthv = {Monthv}
        AND CompanyID = '{UserInfo.UserCompanyId}'
        AND ExtrSource = '{UserInfo.UserExtrSource}'
GROUP BY Yearv ,
        Monthv ,
        Weekv
order by Weekv";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch(Exception ex)
            {
                throw (ex);
            }
        }
    }
}
